
/*                THIS PROGRAM EXTRACTS AND DATES THE FIRST PAYMENT RECORD OF EACH LEASE                        */
/*                     (RUM MACRO FOR EACH STATE USING THE RAW DATA FROM WRDS)                                  */

libname raw 'M:\Data\RentBureau\RawData';
libname rent 'M:\Data\RentBureau\RentData';
libname dir 'D:\My Documents\RESEARCH\RentBureau\Datasets';
run;

%macro rent(input=, output=);

data leases (drop= collection_id moveout_date_collections 
placement_date placed_amt balance_amt collected_amt collection_status);
set raw.&input;
if collection_id=' '; run;

* Creating year and month variables: lease begin_date and last activity_date;

data temp1;
set leases;
movein_yr=year(movein_date);
movein_mo=month(movein_date);
last_act_yr=year(last_activity_date);
last_act_mo=month(last_activity_date);
run;

* Creating Monthly Payment Var from Payment Vector;

data temp3; length month1-month24 $1; set temp1;
month1=substr(rb_pay_vector,1,1);
month2=substr(rb_pay_vector,2,1);
month3=substr(rb_pay_vector,3,1);
month4=substr(rb_pay_vector,4,1);
month5=substr(rb_pay_vector,5,1);
month6=substr(rb_pay_vector,6,1);
month7=substr(rb_pay_vector,7,1);
month8=substr(rb_pay_vector,8,1);
month9=substr(rb_pay_vector,9,1);
month10=substr(rb_pay_vector,10,1);
month11=substr(rb_pay_vector,11,1);
month12=substr(rb_pay_vector,12,1);
month13=substr(rb_pay_vector,13,1);
month14=substr(rb_pay_vector,14,1);
month15=substr(rb_pay_vector,15,1);
month16=substr(rb_pay_vector,16,1);
month17=substr(rb_pay_vector,17,1);
month18=substr(rb_pay_vector,18,1);
month19=substr(rb_pay_vector,19,1);
month20=substr(rb_pay_vector,20,1);
month21=substr(rb_pay_vector,21,1);
month22=substr(rb_pay_vector,22,1);
month23=substr(rb_pay_vector,23,1);
month24=substr(rb_pay_vector,24,1);
run;


proc datasets noprint; delete temp1;
run;
* Inverting Payment Record Series;

data temp4(drop=i month1-month24); set temp3;
array dest {*} $1. mth1 - mth24;			* mth series move forward, mth1 being the 1st observation;
array source {*} month1 - month24;			* month series moves backward;
do i=1 to 24;
   if source(i) in (' ' '.') then dest(25-i)=' ';
   else dest(25-i)=source(i);
end;
run;

proc datasets noprint; delete temp3; run;
* Separating Lease Characteristics from Payment Records;

data lease_char(drop= mth1-mth24) lease_perf(keep= tag resident_id mth1-mth24);
set temp4; run;

proc datasets noprint; delete temp4; run;
* Transposing Payment Records;

proc sort data=lease_perf; by tag resident_id; run;

proc transpose data=lease_perf out=tmp1;
by tag resident_id; var mth1-mth24; run;

data tmp2(rename= (col1=pmt_rec _name_=pmt_mth));  set tmp1;
label col1='pmt_rec' _name_='pmt_mth';
pmt_series=input(substr(_name_,4,2),2.); run;


proc datasets noprint; delete tmp1; run;
* Recombining Lease Characteristics and Payment Records;

proc sql;
create table temp5(drop= _tag _resident_id)as
	select *
    from tmp2, lease_char(rename=(tag=_tag resident_id=_resident_id))
	where tag=_tag and resident_id=_resident_id;

proc sort data=temp5; by tag resident_id; run;

proc datasets noprint; delete tmp2 lease_char lease_perf; run;
* Adding Payment Year and Month for the Last Payment Record;

data temp6; set temp5; by tag resident_id;
if last_act_yr ne .;
if pmt_series=24 then do;
   pmt_year=last_act_yr; pmt_month=last_act_mo; end;
run;

proc datasets noprint; delete temp5; run;
* Adding Payment Year and Month for Earlier Payment Records;

proc sort data=temp6; by tag resident_id descending pmt_series; run;

data temp7(drop= i _year _month);
set temp6; by tag resident_id;
if first.resident_id then do;
	i=1; _month=pmt_month; _year=pmt_year; 
    retain i _month _year; 
    end;
else do;
	pmt_month=_month-i;
	if pmt_month=0 then do;
		pmt_month=12; pmt_year=_year-1; i=1; _year=_year-1; _month=12;
		retain i _year _month; 
        end;
	else do;
		pmt_year=_year;
		i=i+1; retain i; 
		end;
	end;
if pmt_year lt movein_yr then delete;				            * Deleting observations dated prior to movin date;
if pmt_year=movein_yr and pmt_month lt movein_mo then delete;   * Deleting observations dated prior to movin date;
if pmt_rec=' ' then pmt_rec='P';					            * Coding missing payment records as P;
run;


proc datasets noprint; delete temp6; run;
* Selection First Payment Record of Each Lease;
proc sort data=temp7; by tag resident_id pmt_series; run;

data temp8(keep= tag resident_id pmt_rank pmt_rec pmt_series property_id
unit_id city state zip movein_date last_activity_date rent pmt_year pmt_month); 
set temp7(rename= (pmt_mth=pmt_rank));
by tag resident_id pmt_series;
if first.resident_id; 
label pmt_rank='pmt_rank';
run;


proc datasets noprint; delete temp7; run;
* Section to add MSA names and numbers ;
proc sort data=temp8;
	by property_id unit_id pmt_year pmt_month;
	run;

data temp9;
	set temp8;
	pmt_date = mdy(pmt_month,1,pmt_year);
	if rent > 100;
	run;

proc datasets noprint; delete temp8; run;

proc sql;
create table temp10(drop=_zip) as
	select *
	from temp9, dir.zip_msa_matches(drop=state rename=(zip=_zip))
	where substr(zip,1,5)=_zip;
run;


proc datasets noprint; delete temp9; run;
proc sort data=dir.combmsa;
	by msa;
	run;

data combmsa1;
	set dir.combmsa;
		by msa;
		if first.msa;
		run;

proc sql;
create table temp11(drop=_msa) as
	select *
	from temp10, combmsa1(drop=city1 state1 state city rename=(msa=_msa))
	where msa=_msa;
run;

proc datasets noprint; delete temp10; run;
proc sort data=temp11;
	by property_id unit_id pmt_year pmt_month;
	run;

data temp12;
	set temp11;
	by property_id unit_id pmt_year pmt_month;
	if last.pmt_month=1 and first.pmt_month=0 then delete;
	run;

proc datasets noprint; delete temp11; run;
* Output datasets ;
data rent.&output;
	set temp12;

proc export data=rent.&output
	outfile="M:\Data\RentBureau\CSVdata\&output..csv"
	dbms=csv
	replace;
run;

* Clean-up Memory;
proc datasets noprint;
	delete temp1-temp12 leases lease_char lease_perf tmp1 tmp2;
run;
run;

%mend rent;

/*%rent(input=ak_data, output=ak_rents)*/
%rent(input=al_data, output=al_rents)
%rent(input=ar_data, output=ar_rents)
%rent(input=az_data, output=az_rents)
%rent(input=ca_data, output=ca_rents)
%rent(input=co_data, output=co_rents)
%rent(input=ct_data, output=ct_rents)
%rent(input=dc_data, output=dc_rents)
%rent(input=de_data, output=de_rents)
%rent(input=fl_data, output=fl_rents)
%rent(input=ga_data, output=ga_rents)
%rent(input=hi_data, output=hi_rents)
%rent(input=ia_data, output=ia_rents)
%rent(input=id_data, output=id_rents)
%rent(input=il_data, output=il_rents)
%rent(input=in_data, output=in_rents)
%rent(input=ks_data, output=ks_rents)
%rent(input=ky_data, output=ky_rents)
%rent(input=la_data, output=la_rents)
%rent(input=ma_data, output=ma_rents)
%rent(input=md_data, output=md_rents)
%rent(input=me_data, output=me_rents)
%rent(input=mi_data, output=mi_rents)
%rent(input=mn_data, output=mn_rents)
%rent(input=mo_data, output=mo_rents)
%rent(input=ms_data, output=ms_rents)
%rent(input=mt_data, output=mt_rents)
%rent(input=nc_data, output=nc_rents)
%rent(input=nd_data, output=nd_rents)
%rent(input=ne_data, output=ne_rents)
%rent(input=nh_data, output=nh_rents)
%rent(input=nj_data, output=nj_rents)
%rent(input=nv_data, output=nv_rents)
%rent(input=ny_data, output=ny_rents)
%rent(input=oh_data, output=oh_rents)
%rent(input=ok_data, output=ok_rents)
%rent(input=or_data, output=or_rents)
%rent(input=pa_data, output=pa_rents)
%rent(input=pr_data, output=pr_rents)
%rent(input=ri_data, output=ri_rents)
%rent(input=sc_data, output=sc_rents)
%rent(input=sd_data, output=sd_rents)
%rent(input=tn_data, output=tn_rents)
%rent(input=tx_data, output=tx_rents)
%rent(input=ut_data, output=ut_rents)
%rent(input=va_data, output=va_rents)
%rent(input=vt_data, output=vt_rents)
%rent(input=wa_data, output=wa_rents)
%rent(input=wi_data, output=wi_rents)
%rent(input=wv_data, output=wv_rents)
%rent(input=wy_data, output=wy_rents)

/*%rent(input=, output=)
%rent(input=, output=)
%rent(input=, output=)
%rent(input=, output=)
*/

run;

